﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Pub
{
    /// <summary>
    /// 创建Hour表及Day表连续数据视图
    /// </summary>
    public static class CCreatView
    {
        public static void creatHourView(DateTime DatS, DateTime DatE, string sView)
        {
            //CDebug.EnterMethod("创建小时表视图");

            AdoMSSQL ado1 = new AdoMSSQL("DBCon_ZCD");

            //删除视图
            string strSQL = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS "
                + "      WHERE TABLE_NAME = '" + sView + "') "
                + "   DROP VIEW " + sView;
            ado1.cmd1(strSQL);

            //创建视图
            strSQL = "CREATE VIEW " + sView
                + " AS "
                + " SELECT * FROM HIS_HOUR_" + DatS.ToString("yyyyMM") + " \n";

            int i = 1;
            string sTem = DatS.AddMonths(i).ToString("yyyyMM");

            while (Convert.ToInt32(DatS.AddMonths(i).ToString("yyyyMM")) <= Convert.ToInt32(DatE.ToString("yyyyMM")))
            {
                strSQL += " UNION ALL "
                    + " SELECT * FROM HIS_HOUR_" + sTem + " \n";
                sTem = DatS.AddMonths(i).ToString("yyyyMM");
                i++;
            }

            //CDebug.Send(strSQL);

            ado1.cmd1(strSQL);

            //CDebug.ExitMethod("创建小时表视图");
        }

        public static void creatDayView(DateTime DatS, DateTime DatE, string sView)
        {
            //CDebug.EnterMethod("创建Day表视图");

            AdoMSSQL ado1 = new AdoMSSQL("DBCon_ZCD");

            //删除视图
            string strSQL = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS "
                + "      WHERE TABLE_NAME = '" + sView + "') "
                + "   DROP VIEW " + sView;
            ado1.cmd1(strSQL);

            //创建视图
            strSQL = "CREATE VIEW " + sView
                + " AS "
                + " SELECT * FROM HIS_DAY_" + DatS.ToString("yyyy") + " \n";

            int i = 1;
            string sTem = DatS.AddMonths(i).ToString("yyyy");

            while (Convert.ToInt32(DatS.AddYears(i).ToString("yyyy")) <= Convert.ToInt32(DatE.ToString("yyyy")))
            {
                strSQL += " UNION ALL "
                    + " SELECT * FROM HIS_DAY_" + sTem + " \n";
                sTem = DatS.AddMonths(i).ToString("yyyy");
                i++;
            }

            //CDebug.Send(strSQL);

            ado1.cmd1(strSQL);

            //CDebug.ExitMethod("创建Day表视图");
        }


        public static void creatView(DateTime DatS, DateTime DatE, string sViewName, string sTabHead, string sDatFormat)
        {
            CDebug.EnterMethod("创建小时表视图");

            try
            {
                AdoMSSQL ado1 = new AdoMSSQL("DBCon_ZCD");

                //删除视图
                string strSQL = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS "
                    + "      WHERE TABLE_NAME = '" + sViewName + "') "
                    + "   DROP VIEW " + sViewName;
                ado1.cmd1(strSQL);

                //创建视图
                strSQL = "CREATE VIEW " + sViewName
                    + " AS "
                    + " SELECT * FROM " + sTabHead + DatS.ToString(sDatFormat) + " \n";

                int i = 1;
                string sTem = DatS.AddMonths(i).ToString(sDatFormat);

                while (Convert.ToInt32(DatS.AddMonths(i).ToString(sDatFormat)) <= Convert.ToInt32(DatE.ToString(sDatFormat)))
                {
                    strSQL += " UNION ALL "
                        + " SELECT * FROM " + sTabHead + sTem + " \n";
                    sTem = DatS.AddMonths(i).ToString(sDatFormat);
                    i++;
                }

                //CDebug.Send(strSQL);

                ado1.cmd1(strSQL);
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                CDebug.Send("创建视图出错", ex);
            }


            CDebug.ExitMethod("创建小时表视图");
        }

        public static Boolean isView(string sViewName)
        {
            AdoMSSQL ado1 = new AdoMSSQL("DBCon_ZCD");
            string strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS  WHERE TABLE_NAME = '" + sViewName + "'";
            DataSet ds1 = ado1.sel1(strSQL);

            if (CDB.dsIsNull(ds1)) return false;
            else return true;

        }


    }

}
